import json
import requests
import pymysql

#协同平台-航运公司安全管理系统-已办任务
BASE_URL = 'http://cmp.msa.gov.cn/ShippingSecurity/user/getFinishTaskList.action'
POST_URL = 'http://cmp.msa.gov.cn/ShippingSecurity/BusinessFlow_Local.action'
headers = {
    'Accept': '*/*',
    # Content-Type设置为application/json，接口才能返回json数据
    'Content-Type': 'application/x-www-form-urlencoded; charset=UTF-8'
}
cookies = {
    # OAMAuthnCookie_cmp.msa.gov.cn:80 参数用于登录认证
    'OAMAuthnCookie_cmp.msa.gov.cn:80': '48UJcht7MGKpTaHzx12FGSe93E5LbJfBYW5Y3U%2FOMf66gxdIdVPuYmCrcIaMwJASgemW%2F%2F%2FteXQ4%2FUYVI6iKfFXgs7HaSc2YxN3IWRMzSMmpQ1%2FLQL5asM%2Fu4IKOF4QPF5m7WTucQDp6lyY13YI7X%2B9lmRXOdI4NnDAnkI9QGxlpr%2FMcirB2v%2BlqaWGyQAUxxuAZ9XsdKuiWq9br%2F6lY8Oxnq9xC3FHRpkD9ZKbw1EOn%2FYj3jW5%2BV0oZl4%2BsuxGx68hsqXunesy0UkzgtGaFgI2p4K4Tu4GFpZGaCrdwxooQEqJFCvJhtNTGPXpzKeB3c9jIPgcSgZn6jFAQO2u%2BxhYNVU4gEdeVq3m9qFHlN0a%2FGg0IxvGsEJMRjdpjxVbzqG5%2FmZQBSy5uQjXc4psllaQNRscd54Y%2FchcShiXerzgJ%2BwmZwaPfjoMG6jMZg6PqfujLK9wK9lgnavkM9FKW%2B38xGp1ptwu5YZ9DD2OEBEk%3D',
    # OAMAuthnHintCookie和JSESSIONID 参数用于声明返回接口数据，不配置的会一直返回HTML
    'OAMAuthnHintCookie': '1',
    'JSESSIONID': 'TKVQuojVun346ncJdHrqba772D9KjTRNHY4frS8CwapCNSbxVP7o!-661838780'
}

session = requests.Session()

for index in range(0,7):
    print('第'+str(index+1)+'页')
    BASE_URL_PARAM = {
        'JsonValues': '{"table":{"tableName":"Pub_CurrentUser","row":{"CurrentPage":"' + str(index + 1) +'", "PageRecord": "200"}}}'}
    response_lo = session.post(BASE_URL,data=BASE_URL_PARAM, headers=headers, cookies=cookies)
    print(response_lo.text)
    strData = json.loads(json.loads(response_lo.text))
    records = strData['datas']['row']['table']['row']


    if response_lo.status_code == 200:
        # 数据库连接配置
        db_config = {
            'host': '191.254.8.220',
            'user': 'root',
            'password': 'Szmsa@520',
            'db': 'spiders',
            'charset': 'utf8mb4',
            'cursorclass': pymysql.cursors.DictCursor
        }
        # 表名和字段名
        table_name = 'xt_safety_ship_safe_cert_5'
        table_auditor = 'xt_safety_ship_safe_cert_auditor_5'
        # 连接数据库
        connection = pymysql.connect(**db_config)

        try:
            # 开始事务
            with connection.cursor() as cursor:
                # 循环插入每条记录
                for record in records:
                    # 获取需要插入的字段名称
                    fields = ', '.join(record.keys())
                    # 拼装需要插入字段对应的%号个数
                    values = ", ".join(['%s'] * len(record))
                    business_id = record['BusinessId']
                    # 节点名称不为空
                    if record['NodeName'].strip() != "":
                        # 构建插入语句
                        insert_stmt = (
                            "INSERT INTO `{table}` ({fields}) "
                            "VALUES ({values})"
                        ).format(
                            table=table_name,
                            fields=fields,
                            values=values
                        )
                        # 执行sql语句插入数据，record.values()用于获取字段中的值
                        cursor.execute(insert_stmt, tuple(record.values()))
                        # 获取申请单审核员信息
                        POST_URL_PARAM={'MethodValue': 'getAuditOpinon','JsonValues': '{"table":{"tableName":"Pub_Query","row":{"BusinessId":"'+business_id+'"}}}'}
                        response_auditor = session.get(POST_URL,params=POST_URL_PARAM ,headers=headers, cookies=cookies)
                        auditorData = json.loads(json.loads(response_auditor.text))
                        print(auditorData)
                        auditorRecords = auditorData['datas']['table']['row']
                        if response_auditor.status_code == 200:
                            for auditor in auditorRecords:
                                # 船舶安全管理证书核发数量已证书制作审核节点为准
                                if auditor['Node'] == '证书制作':
                                    # 获取需要插入的字段名称
                                    auditor_fields = ', '.join(auditor.keys())
                                    # 拼装需要插入字段对应的%号个数
                                    auditor_values = ", ".join(['%s'] * len(auditor))
                                    insert_auditor = (
                                        "INSERT INTO `{table}` ({fields},BusinessId) "
                                        "VALUES ({values},'{businessId}')"
                                    ).format(
                                        table=table_auditor,
                                        fields=auditor_fields,
                                        values=auditor_values,
                                        businessId=business_id
                                    )
                                    cursor.execute(insert_auditor, tuple(auditor.values()))
            # 提交事务
            connection.commit()
        finally:
            # 关闭数据库连接
            connection.close()
print('插入完成！')